﻿/*********************************************
 创建者：  dmhai
 创建时间：2017-03-15
 功能说明：商品层标准金额占比
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using PlanAPI.model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;

namespace PlanAPI
{
    [Description("商品层标准金额占比")]
    public class PLMatPProcessHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 获取[区域-月-商品层标准金额占比]列表 GetAreaMonthList
        [MustLogin]
        [Description("获取[区域-月-商品层标准金额占比]列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLMatPProcessModel.AreaMonth.ListModel>>))]
        public ApiResult GetAreaMonthList([ParamDescription("商品区域")] string ParentAreaID = "",
          [ParamDescription("商品层")] string MaterialProperty = "",
          [ParamDescription("类型")] string KindID = "",
          [ParamDescription("系列")] string SeriesID = "",
          [ParamDescription("页码")] int Page = 1,
          [ParamDescription("页数")] int Size = 10)
        {
            #region 没企划信息就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.区域;
            int MonthOrWeek = (int)MonthOrWeekEnum.月;

            //整个查询分两步走：①先查询组信息，构造where条件；②根据where查询数据

            #region 先根据前端界面条件查询组信息,构造where条件

            //按当前企划的年月查询
            string groupWhereStr = $"where p.CompanyID = '{UserInfo.UserCompanyId}' and p.Yearv = {plan.Yearv} and p.Monthv = {plan.Monthv} ";
            //加入MonthOrWeek跟AreaOrShop条件
            groupWhereStr += $" and p.MonthOrWeek = {MonthOrWeek} and p.AreaOrShop = {AreaOrShop} ";
            //加入前台传来的参数
            groupWhereStr += string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and p.ParentAreaID = '{ParentAreaID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and p.MaterialProperty = '{MaterialProperty}'";
            groupWhereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and p.KindID = '{KindID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and p.SeriesID = '{SeriesID}'";

            //特殊规则
            //1、不要推品
            groupWhereStr += " and p.SeriesID != '000005'";

            //获取分组条数,将返回到前端做分页显示
            string countSql = GetSqlWithGroup("count(1)", groupWhereStr);
            var countCmd = Helper.GetSqlStringCommand(countSql);
            var groupCount = Convert.ToInt32(Helper.ExecuteScalar(countCmd));

            //这里按组分页，得到需要的信息并且拼接成where条件
            var groupSql = GetSqlWithGroup("*", groupWhereStr);
            string whereSql = GetWhereSql(Page, Size, groupSql);

            #endregion

            #region 根据组信息查询数据
            var result = PaperData<IGrouping<string, PLMatPProcessModel.AreaMonth.ListModel>>.Create(groupCount, Size, Page);
            //where条件不存在就停止，不然会直接读取整个表，内存会爆掉
            if (string.IsNullOrWhiteSpace(whereSql))
            {
                result.Items = new List<IGrouping<string, PLMatPProcessModel.AreaMonth.ListModel>>();
                return Success(result);
            }


            var listSql = @"SELECT *
FROM (	
    SELECT p.CompanyID, p.Yearv, p.MonthOrWeek, p.Monthv, p.Weekv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID, p.MaterialProperty, p.MoveAmountRate, p.SupplyAmountRate, p.SaleAmountRate, p.MSRate, p.StandAmountRate, p.CalcStandAmountRate, p.PtStandAmountRate, p.AutoStandAmountRate, p.CkPtStandAmountRate, 
    --p.CalcCheck, p.Remark, p.CheckState, p.Checker, p.MofifyDTM, p.AddFlag, p.LoadTime, p.EtlDate, p.AvgMSRate, p.SaleAmount, p.SupplyAmount, p.MoveAmount, p.UpdateRate, p.PtUpdateRate
    ROW_NUMBER()OVER(ORDER BY p.CompanyID,p.Yearv,p.Monthv,p.ParentAreaID,p.KindID,p.SeriesID,p.MaterialProperty) AS rowIndex , kind.KindName,series.SeriesName
	FROM ngoods.ngoods_PL_MatP_Process as p
	left join nods.nods_erp_sd_mat_kind as kind on p.ExtrSource = kind.ExtrSource and p.KindID = kind.KindID
    left join nods.nods_erp_sd_mat_series as series on p.ExtrSource = series.ExtrSource and p.SeriesID = series.SeriesID
	{0} 
    order by p.CompanyID,p.Yearv,p.Monthv,p.ParentAreaID,p.KindID,p.SeriesID,p.MaterialProperty
) table1
";

            listSql = string.Format(listSql, whereSql);
            //不再分页
            var cmd = Helper.GetSqlStringCommand(listSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<PLMatPProcessModel.AreaMonth.ListModel>.ConvertToModel(dt);
            #endregion

            #region 分组排序
            var resultList = list.GroupBy(p => p.Yearv + "_" + p.Monthv + "_" + p.ParentAreaID + "_" + p.KindID + "_" + p.SeriesID)
                .ToList();
            //确保有 数据的时候才排序
            if (resultList.Count > 0)
            {
                //区域+月+商品层占比界面，按商品区域+类别+系列+商品层 排序
                resultList = resultList.OrderBy(x => x.FirstOrDefault().Yearv)
                    .ThenBy(x => x.FirstOrDefault().Monthv)
                    .ThenBy(x => x.FirstOrDefault().ParentAreaID)
                    .ThenBy(x => x.FirstOrDefault().KindID)
                    .ThenBy(x => x.FirstOrDefault().SeriesID)
                    .ThenBy(x => x.FirstOrDefault().MaterialProperty)
                    .ToList();
            }
            #endregion

            result.Items = resultList;
            return Success(result);
        }
        #endregion

        #region 获取[区域-周-商品层][标准金额占比]列表 GetAreaWeekList
        [MustLogin]
        [Description("获取[区域-周-商品层][标准金额占比]列表")]
        [ReturnType(typeof(PaperData<PLMatPProcessModel.AreaWeek.ListModel>))]
        public ApiResult GetAreaWeekList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("商品层")] string MaterialProperty = "",
            [ParamDescription("类型")] string KindID = "",
            [ParamDescription("系列")] string SeriesID = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            #region 没企划信息就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.区域;
            int MonthOrWeek = (int)MonthOrWeekEnum.周;


            //整个查询分两步走：①先查询组信息，构造where条件；②根据where查询数据

            #region 先根据前端界面条件查询组信息,构造where条件

            //按当前企划的年月查询
            string groupWhereStr = $"where p.CompanyID = '{UserInfo.UserCompanyId}' and  p.Yearv = {plan.Yearv} and p.Monthv = {plan.Monthv} ";
            //加入MonthOrWeek跟AreaOrShop条件
            groupWhereStr += $" and p.MonthOrWeek = {MonthOrWeek} and p.AreaOrShop = {AreaOrShop} ";
            //加入前台传来的参数
            groupWhereStr += string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and p.ParentAreaID = '{ParentAreaID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and p.MaterialProperty = '{MaterialProperty}'";
            groupWhereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and p.KindID = '{KindID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and p.SeriesID = '{SeriesID}'";

            //特殊规则
            //1、不要推品
            groupWhereStr += " and p.SeriesID != '000005'";

            //获取分组条数,将返回到前端做分页显示
            string countSql = GetSqlWithGroup("count(1)", groupWhereStr);
            var countCmd = Helper.GetSqlStringCommand(countSql);
            var groupCount = Convert.ToInt32(Helper.ExecuteScalar(countCmd));


            //这里按组分页，得到需要的信息并且拼接成where条件
            var groupSql = GetSqlWithGroup("*", groupWhereStr);
            string whereSql = GetWhereSql(Page, Size, groupSql);


            #endregion


            #region 根据组信息查询数据
            var result = PaperData<PLMatPProcessModel.AreaWeek.ListModelInfo>.Create(groupCount, Size, Page);
            //where条件不存在就停止，不然会直接读取整个表，内存会爆掉
            if (string.IsNullOrWhiteSpace(whereSql))
            {
                result.Items = new List<PLMatPProcessModel.AreaWeek.ListModelInfo>();
                return Success(new { Float = new List<string>(), PageData = result });
            }

            var listSql = @" SELECT * FROM (	
SELECT  p.CompanyID, p.Yearv, p.MonthOrWeek, p.Monthv, p.Weekv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID, p.MaterialProperty, p.MoveAmountRate, p.SupplyAmountRate, p.SaleAmountRate, p.MSRate, p.StandAmountRate, p.CalcStandAmountRate, p.PtStandAmountRate, p.AutoStandAmountRate, p.CkPtStandAmountRate, 
--p.CalcCheck, p.Remark, p.CheckState, p.Checker, p.MofifyDTM, p.AddFlag, p.LoadTime, p.EtlDate, p.AvgMSRate, p.SaleAmount, p.SupplyAmount, p.MoveAmount, p.UpdateRate, p.PtUpdateRate
(select MonthWeekFloat from ngoods.ngoods_PL_Bas_MatPFloat where CompanyID = '{0}' and   MaterialProperty = p.MaterialProperty) as 'FloatValue',
ROW_NUMBER()OVER(ORDER BY p.CompanyID,p.Yearv,p.Monthv,p.ParentAreaID,p.KindID,p.SeriesID,p.MaterialProperty) AS rowIndex, kind.KindName,series.SeriesName ,
(
    select  max(CkPtStandAmountRate) from ngoods.ngoods_PL_MatP_Process 
    where CompanyID = '{0}' and Yearv = p.Yearv and Monthv = p.Monthv and ParentAreaID = p.ParentAreaID and KindID = p.KindID and SeriesID = p.SeriesID and MaterialProperty = p.MaterialProperty and AreaOrShop = 0 and MonthOrWeek = 0
) as  'AreaMonth_CkPtStandAmountRate'
FROM ngoods.ngoods_PL_MatP_Process as p
left join nods.nods_erp_sd_mat_kind as kind on p.ExtrSource = kind.ExtrSource and p.KindID = kind.KindID
left join nods.nods_erp_sd_mat_series as series on p.ExtrSource = series.ExtrSource and p.SeriesID = series.SeriesID
{1}
order by p.CompanyID,p.Yearv,p.Monthv,p.ParentAreaID,p.KindID,p.SeriesID,p.Weekv,p.MaterialProperty
) table1  ";

            listSql = string.Format(listSql , UserInfo.UserCompanyId, whereSql);
            //不再分页
            var cmd = Helper.GetSqlStringCommand(listSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<PLMatPProcessModel.AreaWeek.ListModel>.ConvertToModel(dt);

            #endregion

            #region 计算区间

            foreach (var model in list)
            {
                ////设置区间
                model.FloatAreaLeft = model.AreaMonth_CkPtStandAmountRate - model.AreaMonth_CkPtStandAmountRate * model.FloatValue;
                model.FloatAreaRight = model.AreaMonth_CkPtStandAmountRate + model.AreaMonth_CkPtStandAmountRate * model.FloatValue;
            }
            #endregion

            #region 分组排序

            var resultList = new List<PLMatPProcessModel.AreaWeek.ListModelInfo>();
            //二次分组
            var tmpList = list.GroupBy(x => new { x.Yearv, x.Monthv, x.ParentAreaID, x.KindID, x.SeriesID }, (model, modelGroup) => new
            {
                Item = model,
                Datas = modelGroup.GroupBy(y => new { y.Weekv }, (item, itemGroup) => new
                {
                    Item = item,
                    ItemGroup = itemGroup.OrderBy(x => x.Weekv)
                })
            });

            //区域+周+商品层占比界面，按商品区域+类别+系列+周+商品层 排序
            tmpList = tmpList.OrderBy(x => x.Item.Yearv).ThenBy(x => x.Item.Monthv).ThenBy(x => x.Item.ParentAreaID).ThenBy(x => x.Item.KindID).ThenBy(x => x.Item.SeriesID);

            #endregion

            #region 遍历分组结果组合数据集供前端使用

            tmpList.ToList().ForEach(g1 =>
            {
                var tmpModel = new PLMatPProcessModel.AreaWeek.ListModelInfo
                {
                    Yearv = g1.Item.Yearv,
                    Monthv = g1.Item.Monthv,
                    KindID = g1.Item.KindID,
                    SeriesID = g1.Item.SeriesID
                };

                g1.Datas.OrderBy(x => x.Item.Weekv).ToList().ForEach(g2 =>
                {
                    var weekGroup = new PLMatPProcessModel.AreaWeek.WeekGroup(g2.Item.Weekv);
                    g2.ItemGroup.ToList().ForEach(model => {
                        weekGroup.WeekList.Add(model);
                    });
                    tmpModel.List.Add(weekGroup);
                });

                resultList.Add(tmpModel);
            });
            #endregion 

            result.Items = resultList.ToList();
            //获取浮动值列表
            var floatList = new PLBasModelFloatHandler().GetMonthWeekFloatList();
            return Success(new { Float = floatList, PageData = result });
        }
        #endregion

        #region 获取[单店-月-商品层][标准金额占比]列表 GetShopMonthList

        [MustLogin]
        [Description("获取[单店-月-商品层][标准金额占比]列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLMatPProcessModel.ShopMonth.ListModel>>))]
        public ApiResult GetShopMonthList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("商品层")] string MaterialProperty = "",
            [ParamDescription("类型")] string KindID = "",
            [ParamDescription("系列")] string SeriesID = "",
            [ParamDescription("店铺ID或名称")] string Shop = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            #region 没企划信息就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.单店;
            int MonthOrWeek = (int)MonthOrWeekEnum.月;


            //整个查询分两步走：①先查询组信息，构造where条件；②根据where查询数据

            #region 先根据前端界面条件查询组信息,构造where条件

            //按当前企划的年月查询
            string groupWhereStr = $"where p.CompanyID = '{UserInfo.UserCompanyId}' and  p.Yearv = {plan.Yearv} and p.Monthv = {plan.Monthv} ";
            //加入MonthOrWeek跟AreaOrShop条件
            groupWhereStr += $" and p.MonthOrWeek = {MonthOrWeek} and p.AreaOrShop = {AreaOrShop} ";
            //加入前台传来的参数
            groupWhereStr += string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and p.ParentAreaID = '{ParentAreaID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and p.MaterialProperty = '{MaterialProperty}'";
            groupWhereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and p.KindID = '{KindID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and p.SeriesID = '{SeriesID}'";
            //加入查询到店粒度专用参数
            if (!string.IsNullOrWhiteSpace(Shop))
            {
                Shop = "'" + Shop.Replace(",", "','").Trim('\'') + "'";
                groupWhereStr += $" and shop.ShopID in ({Shop}) ";
            }
            //特殊规则
            //1、不要推品
            groupWhereStr += " and p.SeriesID != '000005'";

            //获取分组条数,将返回到前端做分页显示
            string countSql = GetSqlWithGroup("count(1)", groupWhereStr);
            var countCmd = Helper.GetSqlStringCommand(countSql);
            var groupCount = Convert.ToInt32(Helper.ExecuteScalar(countCmd));


            //这里按组分页，得到需要的信息并且拼接成where条件
            var groupSql = GetSqlWithGroup("*", groupWhereStr);
            string whereSql = GetWhereSql(Page, Size, groupSql);


            #endregion

            #region 根据组信息查询数据
            var result = PaperData<IGrouping<string, PLMatPProcessModel.ShopMonth.ListModel>>.Create(groupCount, Size, Page);
            //where条件不存在就停止，不然会直接读取整个表，内存会爆掉
            if (string.IsNullOrWhiteSpace(whereSql))
            {
                result.Items = new List<IGrouping<string, PLMatPProcessModel.ShopMonth.ListModel>>();
                return Success(result);
            }

            var listSql = @"SELECT * FROM (
SELECT p.CompanyID, p.Yearv, p.MonthOrWeek, p.Monthv, p.Weekv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID, p.MaterialProperty, p.MoveAmountRate, p.SupplyAmountRate, p.SaleAmountRate, p.MSRate, p.StandAmountRate, p.CalcStandAmountRate, p.PtStandAmountRate, p.AutoStandAmountRate, p.CkPtStandAmountRate, 
--p.CalcCheck, p.Remark, p.CheckState, p.Checker, p.MofifyDTM, p.AddFlag, p.LoadTime, p.EtlDate, p.AvgMSRate, p.SaleAmount, p.SupplyAmount, p.MoveAmount, p.UpdateRate, p.PtUpdateRate
ROW_NUMBER()OVER(ORDER BY p.CompanyID,p.Yearv,p.Monthv,p.ParentAreaID,p.KindID,p.SeriesID,p.MaterialProperty) AS rowIndex, shop.ShopName ,kind.KindName,series.SeriesName,
(
    select max(CkPtStandAmountRate) 
    from ngoods.ngoods_PL_MatP_Process p2 
    where p2.CompanyID = '{0}' and p2.Yearv = p.Yearv and p2.Monthv = p.Monthv and p2.ParentAreaID = p.ParentAreaID and p2.KindID = p.KindID and p2.SeriesID = p.SeriesID and p2.MaterialProperty = p.MaterialProperty and p2.AreaOrShop = 0 and p2.MonthOrWeek = 0
) as 'AreaMonth_CkPtStandAmountRate',
(
select AreaShopFloat from ngoods.ngoods_PL_Bas_MatPFloat where CompanyID = '{0}' and MaterialProperty = p.MaterialProperty
) as 'FloatValue'
FROM ngoods.ngoods_PL_MatP_Process p 
LEFT JOIN ndw.ndw_Shop AS shop ON p.CompanyID = shop.CompanyID and p.ShopID = shop.ShopID 
LEFT JOIN nods.nods_erp_sd_mat_kind AS kind ON p.ExtrSource = kind.ExtrSource and p.KindID = kind.KindID 
LEFT JOIN nods.nods_erp_sd_mat_series AS series ON p.ExtrSource = series.ExtrSource and p.SeriesID = series.SeriesID 
{1}
order by p.CompanyID,p.Yearv,p.Monthv,p.ParentAreaID,p.ShopID,p.KindID,p.SeriesID,p.MaterialProperty 
) table1  ";

            listSql = string.Format(listSql, UserInfo.UserCompanyId, whereSql);
            //不再分页
            var cmd = Helper.GetSqlStringCommand(listSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<PLMatPProcessModel.ShopMonth.ListModel>.ConvertToModel(dt);

            #endregion

            #region 计算区间
            foreach (var model in list)
            {
                model.FloatAreaLeft = model.AreaMonth_CkPtStandAmountRate - model.AreaMonth_CkPtStandAmountRate * model.FloatValue;
                model.FloatAreaRight = model.AreaMonth_CkPtStandAmountRate + model.AreaMonth_CkPtStandAmountRate * model.FloatValue;
            }
            #endregion

            #region 分组排序
            var resultList = list.GroupBy(p => p.Yearv + "_" + p.Monthv + "_" + p.ShopID + "_" + p.ParentAreaID + "_" + p.KindID + "_" + p.SeriesID)
               .ToList();
            //确保有 数据的时候才排序
            if (resultList.Count > 0)
            {
                //单店+月+商品层占比界面，按商品区域+店铺+类别+系列+商品层 排序
                resultList = resultList.OrderBy(x => x.FirstOrDefault().Yearv)
                    .ThenBy(x => x.FirstOrDefault().Monthv)
                    .ThenBy(x => x.FirstOrDefault().ParentAreaID)
                    .ThenBy(x => x.FirstOrDefault().ShopID)
                    .ThenBy(x => x.FirstOrDefault().KindID)
                    .ThenBy(x => x.FirstOrDefault().SeriesID)
                    .ThenBy(x => x.FirstOrDefault().MaterialProperty)
                    .ToList();
            }
            #endregion

            result.Items = resultList;
            return Success(result);
        }

        #endregion

        #region 获取[单店-周-商品层][标准金额占]比列表 GetShopWeekList
        [MustLogin]
        [Description("获取[单店-周-商品层][标准金额占]比列表")]
        [ReturnType(typeof(PaperData<PLMatPProcessModel.ShopWeek.ListModel>))]
        public ApiResult GetShopWeekList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("商品层")] string MaterialProperty = "",
            [ParamDescription("类型")] string KindID = "",
            [ParamDescription("系列")] string SeriesID = "",
            [ParamDescription("店铺ID或名称")] string Shop = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 100)
        {
            #region 没企划信息就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.单店;
            int MonthOrWeek = (int)MonthOrWeekEnum.周;

            //整个查询分两步走：①先查询组信息，构造where条件；②根据where查询数据

            #region 先根据前端界面条件查询组信息,构造where条件

            //按当前企划的年月查询
            string groupWhereStr = $"where p.CompanyID = '{UserInfo.UserCompanyId}' and  p.Yearv = {plan.Yearv} and p.Monthv = {plan.Monthv} ";
            //加入MonthOrWeek跟AreaOrShop条件
            groupWhereStr += $" and p.MonthOrWeek = {MonthOrWeek} and p.AreaOrShop = {AreaOrShop} ";
            //加入前台传来的参数
            groupWhereStr += string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and p.ParentAreaID = '{ParentAreaID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and p.MaterialProperty = '{MaterialProperty}'";
            groupWhereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and p.KindID = '{KindID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and p.SeriesID = '{SeriesID}'";
            //加入查询到店粒度专用参数
            if (!string.IsNullOrWhiteSpace(Shop))
            {
                Shop = "'" + Shop.Replace(",", "','").Trim('\'') + "'";
                groupWhereStr += $" and shop.ShopID in ({Shop}) ";
            }
            //特殊规则
            //1、不要推品
            groupWhereStr += " and p.SeriesID != '000005'";

            //获取分组条数,将返回到前端做分页显示
            string countSql = GetSqlWithGroup("count(1)", groupWhereStr);
            var countCmd = Helper.GetSqlStringCommand(countSql);
            var groupCount = Convert.ToInt32(Helper.ExecuteScalar(countCmd));


            //这里按组分页，得到需要的信息并且拼接成where条件
            var groupSql = GetSqlWithGroup("*", groupWhereStr);
            string whereSql = GetWhereSql(Page, Size, groupSql);


            #endregion

            #region 根据组信息查询数据
            var result = PaperData<PLMatPProcessModel.ShopWeek.ListModelInfo>.Create(groupCount, Size, Page);
            //where条件不存在就停止，不然会直接读取整个表，内存会爆掉
            if (string.IsNullOrWhiteSpace(whereSql))
            {
                result.Items = new List<PLMatPProcessModel.ShopWeek.ListModelInfo>();
                return Success(new { Float = new List<string>(), PageData = result });
            }

            var listSql = @" SELECT * FROM (	
    SELECT p.CompanyID, p.Yearv, p.MonthOrWeek, p.Monthv, p.Weekv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID, p.MaterialProperty, p.MoveAmountRate, p.SupplyAmountRate, p.SaleAmountRate, p.MSRate, p.StandAmountRate, p.CalcStandAmountRate, p.PtStandAmountRate, p.AutoStandAmountRate, p.CkPtStandAmountRate, 
--p.CalcCheck, p.Remark, p.CheckState, p.Checker, p.MofifyDTM, p.AddFlag, p.LoadTime, p.EtlDate, p.AvgMSRate, p.SaleAmount, p.SupplyAmount, p.MoveAmount, p.UpdateRate, p.PtUpdateRate
ROW_NUMBER()OVER(ORDER BY p.CompanyID,p.Yearv,p.Monthv,p.ParentAreaID,p.KindID,p.SeriesID,p.MaterialProperty) AS rowIndex ,shop.ShopName,kind.KindName,series.SeriesName,
 (
      select max(CkPtStandAmountRate) 
      from ngoods.ngoods_PL_MatP_Process p2 
      where p2.CompanyID = '{0}' and p2.Yearv = p.Yearv and p2.Monthv = p.Monthv and p2.Weekv = p.Weekv and p2.ParentAreaID = p.ParentAreaID and p2.KindID = p.KindID and p2.SeriesID = p.SeriesID and p2.MaterialProperty = p.MaterialProperty  and p2.AreaOrShop = 0 and p2.MonthOrWeek = 1
 ) as 'AreaWeek_CkPtStandAmountRate',
 (
      select max(CkPtStandAmountRate) 
      from ngoods.ngoods_PL_MatP_Process p2 
      where p2.CompanyID = '{0}' and p2.Yearv = p.Yearv and p2.Monthv = p.Monthv and p2.ParentAreaID = p.ParentAreaID and p2.KindID = p.KindID and p2.SeriesID = p.SeriesID and p2.MaterialProperty = p.MaterialProperty and p2.ShopID = p.ShopID and p2.AreaOrShop = 1 and p2.MonthOrWeek = 0
 ) as 'ShopMonth_CkPtStandAmountRate',
(select AreaShopFloat from ngoods.ngoods_PL_Bas_MatPFloat where CompanyID = '{0}' and   MaterialProperty = p.MaterialProperty) as 'FloatValue1',
(select MonthWeekFloat from ngoods.ngoods_PL_Bas_MatPFloat where CompanyID = '{0}' and MaterialProperty = p.MaterialProperty) as 'FloatValue2'
FROM ngoods.ngoods_PL_MatP_Process p 
left join ndw.ndw_Shop AS shop ON p.CompanyID = shop.CompanyID and p.ShopID = shop.ShopID
left join nods.nods_erp_sd_mat_kind as kind on p.ExtrSource = kind.ExtrSource and p.KindID = kind.KindID
left join nods.nods_erp_sd_mat_series as series on p.ExtrSource = series.ExtrSource and p.SeriesID = series.SeriesID		
{1}
order by p.CompanyID,p.Yearv,p.Monthv,p.ParentAreaID,p.ShopID,p.KindID,p.SeriesID,p.Weekv,p.MaterialProperty 
) table1  ";

            listSql = string.Format(listSql,UserInfo.UserCompanyId, whereSql);
            //不再分页
            var cmd = Helper.GetSqlStringCommand(listSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<PLMatPProcessModel.ShopWeek.ListModel>.ConvertToModel(dt);


            #endregion

            #region 计算区间

            //先总体全部更新一遍，计算自动取值（不全部更新的话，用未更新的值统计就不对了）
            foreach (var model in list)
            {
                model.FloatAreaLeft = model.AreaWeek_CkPtStandAmountRate - model.AreaWeek_CkPtStandAmountRate * model.FloatValue1;
                model.FloatAreaRight = model.AreaWeek_CkPtStandAmountRate + model.AreaWeek_CkPtStandAmountRate * model.FloatValue1;

                model.FloatAreaLeft2 = model.ShopMonth_CkPtStandAmountRate - model.ShopMonth_CkPtStandAmountRate * model.FloatValue2;
                model.FloatAreaRight2 = model.ShopMonth_CkPtStandAmountRate + model.ShopMonth_CkPtStandAmountRate * model.FloatValue2;
            }

            foreach (var model in list)
            {
                var floatValueList = new decimal[] { model.FloatAreaLeft, model.FloatAreaLeft2, model.FloatAreaRight, model.FloatAreaRight2 };
                model.FloatAreaLeft = floatValueList.Min();
                model.FloatAreaRight = floatValueList.Max();
            }

            #endregion

            #region 分组排序
            //先分组
            var tmpList = list.GroupBy(x => new { x.Yearv, x.Monthv, x.ShopID, x.ParentAreaID, x.KindID, x.SeriesID }, (model, modelGroup) => new
            {
                Item = model,
                Datas = modelGroup.GroupBy(y => new { y.Weekv }, (item, itemGroup) => new
                {
                    Item = item,
                    ItemGroup = itemGroup
                })
            });

            var resultList = new List<PLMatPProcessModel.ShopWeek.ListModelInfo>();

            //单店+周+商品层占比界面，按商品区域+店铺+类别+系列+周+商品层 排序
            tmpList = tmpList.OrderBy(x => x.Item.Yearv)
                .ThenBy(x => x.Item.Monthv)
                .ThenBy(x => x.Item.ParentAreaID)
                .ThenBy(x => x.Item.ShopID)
                .ThenBy(x => x.Item.KindID)
                .ThenBy(x => x.Item.SeriesID);


            #endregion

            #region 遍历分组结果组合数据集供前端使用
            tmpList.ToList().ForEach(g1 =>
            {
                var tmpModel = new PLMatPProcessModel.ShopWeek.ListModelInfo
                {
                    Yearv = g1.Item.Yearv,
                    Monthv = g1.Item.Monthv,
                    KindID = g1.Item.KindID,
                    SeriesID = g1.Item.SeriesID
                };

                g1.Datas.OrderBy(x=>x.Item.Weekv).ToList().ForEach(g2 =>
                {
                    var weekGroup = new PLMatPProcessModel.ShopWeek.WeekGroup(g2.Item.Weekv);
                    g2.ItemGroup.ToList().ForEach(model => {
                        weekGroup.WeekList.Add(model);
                    });
                    tmpModel.List.Add(weekGroup);
                });

                resultList.Add(tmpModel);
            });
            #endregion 

            result.Items = resultList.ToList();
            //获取浮动值列表
            var floatList = new PLBasModelFloatHandler().GetAreaShopFloatList();
            return Success(new { Float = floatList, PageData = result });
        }
        #endregion




        #region 修改[区域-月][确认标准金额百分比] UpdateAreaMonth
        [MustLogin]
        [RoleBc]
        [JsonType]
        [Description("修改[区域-月]的[确认标准金额百分比]")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateAreaMonth(PLMatPProcessModel.AreaMonth.UpdateModelList list)
        {
            #region 验证前端传参
            if (list?.List == null || list.List.Count == 0)
            {
                return Error("参数获取失败，请刷新重试");
            }
            #endregion

            #region 是否在企划期，不是就直接返回
            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }
            #endregion

            #region 校验确认值是否正确
            //确认值，可手动修改,相同月份，商品区域，系列下商品层相加是100%
            var ckSum = list.List.GroupBy(p => new { p.Yearv, p.Monthv, p.ParentAreaID, p.MaterialProperty, p.KindID, p.SeriesID })
                        .Select(p => p.Sum(i => i.CkPtStandAmountRate))
                        .FirstOrDefault();
            if (ckSum > 100 || ckSum < 0)
            {
                return Error($"确认值之和的值必须在[0,100]区间内，当前为：{ckSum}%");
            }
            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.区域;
            int MonthOrWeek = (int)MonthOrWeekEnum.月;

            StringBuilder sb = new StringBuilder();
            try
            {
                foreach (var item in list.List)
                {
                    decimal ckPtStandAmountRate = item.CkPtStandAmountRate / 100;
                    var updateSql = $@"UPDATE ngoods.ngoods_PL_MatP_Process SET CkPtStandAmountRate = {ckPtStandAmountRate}, CheckState = 1 
WHERE CompanyID = '{UserInfo.UserCompanyId}' and  MonthOrWeek = {MonthOrWeek} and AreaOrShop = {AreaOrShop} and Yearv = {plan.Yearv} and Monthv = {plan.Monthv} and 
ParentAreaID = '{item.ParentAreaID}' and KindID = '{item.KindID}' and SeriesID = '{ item.SeriesID}' and MaterialProperty = '{item.MaterialProperty}' ;";

                    sb.AppendLine(updateSql);
                }
                int n = Helper.ExecuteSqlTran(new List<CommandInfo>() {new CommandInfo(sb.ToString(), null)});

                if (n > 0)
                {
                    var tmp = list.List.FirstOrDefault();
                    UpdateAreaWeekData(tmp.CompanyID, tmp.Yearv, tmp.Monthv, tmp.ParentAreaID, tmp.KindID, tmp.SeriesID);
                    UpdateShopMonthData(tmp.CompanyID, tmp.Yearv, tmp.Monthv, tmp.ParentAreaID, tmp.KindID, tmp.SeriesID);
                    UpdateShopWeekData(tmp.CompanyID, tmp.Yearv, tmp.Monthv, tmp.ParentAreaID, tmp.KindID, tmp.SeriesID);
                    return Success(0);
                }
                return Error("修改失败，请刷新页面重试");
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 修改[区域-周]的[确认标准金额百分比] UpdateAreaWeek

        [MustLogin]
        [JsonType]
        [RoleBc]
        [Description("修改[区域-周]的[确认标准金额百分比]")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateAreaWeek(PLMatPProcessModel.AreaWeek.UpdateModelList list)
        {
            #region 验证前端传参

            if (list?.List == null || list.List.Count == 0)
            {
                return Error("参数获取失败，请刷新重试");
            }

            #endregion

            #region 验证是否在企划期，不是就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            #region 校验输入的值是否正确

            //验证1. 每一周下的所有商品层的确认标准金额百分比相加是100%；如：周＝1，基本款45%+形象款55%，刚刚好，没毛病；不等于100%就要提示了。
            //直接按照年、月商品区域、周分组，然后各组求和即可
            var ckSum =
                list.List.GroupBy(
                        p => new {p.Yearv, p.Monthv, p.ParentAreaID, p.MaterialProperty, p.KindID, p.SeriesID, p.Weekv})
                    .Select(p => p.Sum(i => i.CkPtStandAmountRate))
                    .ToList();
            foreach (var d in ckSum)
            {
                if (d > 100 || d < 0)
                {
                    return Error($"每一周下的所有商品层的确认标准金额百分比相加和必须在[0,100]区间内，当前为：{d * 100}%");
                }
            }

            #endregion

            int AreaOrShop = (int) AreaOrShopEnum.区域;
            int MonthOrWeek = (int) MonthOrWeekEnum.周;

            var cmdList = new List<CommandInfo>();
            try
            {
                foreach (var item in list.List)
                {
                    decimal ckPtStandAmountRate = item.CkPtStandAmountRate / 100;
                    string sql = $@"UPDATE ngoods.ngoods_PL_MatP_Process 
SET CkPtStandAmountRate = {ckPtStandAmountRate}, CheckState = 1 
WHERE  
CompanyID = '{UserInfo.UserCompanyId}' and  
MonthOrWeek = {MonthOrWeek} and AreaOrShop = {AreaOrShop} and 
Yearv = {plan.Yearv} and Monthv = {plan.Monthv} and Weekv = {item.Weekv} and 
ParentAreaID = '{item.ParentAreaID}' and MaterialProperty = '{item.MaterialProperty}' and 
KindID = '{item.KindID}' and SeriesID = '{item.SeriesID}' ";

                    cmdList.Add(new CommandInfo(sql, null));
                }

                //修改自己本身的确认值
                int n = Helper.ExecuteSqlTran(cmdList);
                if (n > 0)
                {
                    var tmp = list.List.FirstOrDefault();
                    UpdateShopWeekData(tmp.CompanyID, tmp.Yearv, tmp.Monthv, tmp.ParentAreaID, tmp.KindID, tmp.SeriesID);
                    return Success(0);
                }
                return Error("修改失败，请刷新页面重试");
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 修改[单店-月]的[确认标准金额百分比] UpdateShopMonth

        [MustLogin]
        [JsonType]
        [Description("修改[单店-月]的[确认标准金额百分比]")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateShopMonth(PLMatPProcessModel.ShopMonth.UpdateModelList list)
        {
            #region 验证前端传参
            if (list?.List == null || list.List.Count == 0)
            {
                return Error("参数获取失败，请刷新重试");
            }
            #endregion

            #region 验证是否在企划期，不是就直接返回
            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }
            #endregion

            #region 校验输入的值是否正确

            var ckSum = list.List.GroupBy(p => new { p.Yearv, p.Monthv, p.ParentAreaID, p.MaterialProperty, p.ShopID, p.KindID, p.SeriesID })
                                   .Select(p => p.Sum(i => i.CkPtStandAmountRate))
                                   .FirstOrDefault();
            if (ckSum > 100 || ckSum < 0)
            {
                return Error($"确认值之和的值必须在[0,100]区间内，当前为：{ckSum}%");
            }

            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.单店;
            int MonthOrWeek = (int)MonthOrWeekEnum.月;

            var cmdList = new List<CommandInfo>();
            try
            {
                foreach (var item in list.List)
                {
                    string sql = $@"UPDATE ngoods.ngoods_PL_MatP_Process 
SET CkPtStandAmountRate = {item.CkPtStandAmountRate / 100},CheckState = 1 
WHERE  
CompanyID = '{UserInfo.UserCompanyId}' and  
MonthOrWeek = {MonthOrWeek} and AreaOrShop = {AreaOrShop} and 
Yearv = {plan.Yearv} and Monthv = {plan.Monthv} and 
ShopID = '{item.ShopID}' and ParentAreaID = '{item.ParentAreaID}' and MaterialProperty = '{item.MaterialProperty}' and 
KindID = '{item.KindID}' and SeriesID = '{item.SeriesID}' ";
                    cmdList.Add(new CommandInfo(sql, null));

                }

                //修改
                int n = Helper.ExecuteSqlTran(cmdList);
                if (n <= 0)
                {
                    return Error("修改失败，请刷新页面重试");
                }
                var tmp = list.List.FirstOrDefault();
                UpdateShopWeekData(tmp.CompanyID, tmp.Yearv, tmp.Monthv, tmp.ParentAreaID, tmp.KindID, tmp.SeriesID);
                return Success(0);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 修改[单店-周]的[确认标准金额百分比] UpdateShopWeek
        [JsonType]
        [MustLogin]
        [Description("修改[单店-周]的[确认标准金额百分比]")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateShopWeek(PLMatPProcessModel.ShopWeek.UpdateModelList list)
        {
            #region 验证是否在企划期，不是就直接返回
            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }
            #endregion

            #region 校验输入的值是否正确

            //验证1. 1. 每一周下的所有商品层的确认标准金额百分比相加是100%；如：周＝1，基本款45%+形象款55%，刚刚好，没毛病；不等于100 % 就要提示了。
            //直接按照年、月商品区域、周分组，然后各组求和即可(不管有几个商品层)
            var weekSum = list.List.GroupBy(p => new { p.Yearv, p.Monthv, p.ParentAreaID, p.MaterialProperty, p.ShopID, p.KindID, p.SeriesID, p.Weekv })
                    .Select(p => p.Sum(i => i.CkPtStandAmountRate))
                    .ToList();
            foreach (var d in weekSum)
            {
                if (d > 100 || d < 0)
                {
                    return Error($"每一周下的所有商品层的确认标准金额百分比相加和必须在[0,100]区间内，当前为：{d * 100}%");
                }
            }
            
            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.单店;
            int MonthOrWeek = (int)MonthOrWeekEnum.周;

            var cmdList = new List<CommandInfo>();

            try
            {
                foreach (var item in list.List)
                {
                    string sql = $@"UPDATE ngoods.ngoods_PL_MatP_Process 
SET CkPtStandAmountRate = {item.CkPtStandAmountRate / 100}, CheckState = 1 
WHERE
CompanyID = '{UserInfo.UserCompanyId}' and    
MonthOrWeek = {MonthOrWeek} and AreaOrShop = {AreaOrShop} and 
Yearv = {plan.Yearv} and Monthv = {plan.Monthv} and Weekv = {item.Weekv} and 
ShopID = '{item.ShopID}' and ParentAreaID = '{item.ParentAreaID}' and 
MaterialProperty = '{item.MaterialProperty}' and 
KindID = '{item.KindID}' and SeriesID = '{item.SeriesID}' ";

                    cmdList.Add(new CommandInfo(sql, null));
                }

                //修改
                int n = Helper.ExecuteSqlTran(cmdList);
                if (n <= 0)
                {
                    return Error("修改失败，请刷新页面重试");
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion


        #region 获取分组SQL之后的语句 GetSqlWithGroup

        private string GetSqlWithGroup(string field, string where)
        {
            //周不要加入分组
            var sqlWithGroup = @"select {0} from (
    select  ROW_NUMBER()OVER(ORDER BY p.CompanyID, p.Yearv, p.MonthOrWeek, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID) AS rowIndex, 
    count(SeriesID) as RowCount,p.CompanyID, p.Yearv, p.MonthOrWeek, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID
    FROM ngoods.ngoods_PL_MatP_Process p
    left join ndw.ndw_shop shop on p.CompanyID = shop.CompanyID and p.ShopID = shop.ShopID
    {1}
    group by p.CompanyID, p.Yearv, p.MonthOrWeek, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID
    order by p.CompanyID, p.Yearv, p.MonthOrWeek, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID
) table1 ";

            return string.Format(sqlWithGroup, field, where);
        }
        #endregion

        #region 获取分组之后的数据并转换成where条件

        /// <summary>
        /// 获取分组之后的数据并转换成where条件
        /// </summary>
        /// <param name="page">页数</param>
        /// <param name="size">每页组数</param>
        /// <param name="groupSql"></param>
        /// <returns></returns>
        private string GetWhereSql(int page, int size, string groupSql)
        {
            //先拿到分组数据，得到所需的字段数据
            if (page != 0 && size != 0)
            {
                groupSql += string.Format($@" where rowIndex BETWEEN ({page}-1)*{size}+1 AND {page}*{size} ");
            }
            var cmd = Helper.GetSqlStringCommand(groupSql);
            var groupDataTable = Helper.ExecuteDataTable(cmd);
            var groupList = ModelConvertHelper<PLMatPProcessModel.GroupModel>.ConvertToModel(groupDataTable);

            //开始拼接条件
            StringBuilder sb = new StringBuilder($"  where p.CompanyID = '{UserInfo.UserCompanyId}' and");
            for (int i = 0; i < groupList.Count; i++)
            {
                var model = groupList[i];

                sb.Append($" (p.CompanyID='{model.CompanyID}' and p.Yearv={model.Yearv} and p.MonthOrWeek={model.MonthOrWeek} and p.Monthv={model.Monthv} and p.AreaOrShop={model.AreaOrShop} ");

                //if (model.Weekv != 0)
                //{
                //    sb.Append($" and p.Weekv={model.Weekv} ");
                //}

                if (!string.IsNullOrWhiteSpace(model.ParentAreaID))
                {
                    sb.Append($" and p.ParentAreaID='{model.ParentAreaID}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.MaterialProperty))
                {
                    sb.Append($" and p.MaterialProperty='{model.MaterialProperty}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.ShopID))
                {
                    sb.Append($" and p.ShopID='{model.ShopID}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.KindID))
                {
                    sb.Append($" and p.KindID='{model.KindID}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.SeriesID))
                {
                    sb.Append($" and p.SeriesID='{model.SeriesID}' ");
                }

                sb.Append(")");

                if (i != groupList.Count - 1)
                {
                    sb.Append(" or\r\n ");
                }
            }
            var result = sb.ToString().Trim("and".ToCharArray());
            return result;
        }
        #endregion


        #region 重算区域周的数据

        public void UpdateAreaWeekData(string companyId, int year, int month, string parentAreaId, string kindId, string seriesId)
        {
            try
            {
                var sqlPtStandAmountRate = $@"update ngoods.ngoods_PL_MatP_Process a
            set PtStandAmountRate=case when a.CalcStandAmountRate>PtStandAmountRate_p then PtStandAmountRate_p  when a.CalcStandAmountRate<PtStandAmountRate_m then PtStandAmountRate_m  else a.CalcStandAmountRate  end
            from (
                select distinct a.CompanyID,a.Yearv,a.Monthv,a.ParentAreaID,a.KindID,a.SeriesID,a.MaterialProperty,CkPtStandAmountRate*(1+isnull(b.MonthWeekFloat,0.1)) as PtStandAmountRate_p,CkPtStandAmountRate*(1-isnull(b.MonthWeekFloat,0.1)) as PtStandAmountRate_m
                from ngoods.ngoods_PL_MatP_Process A
                left join ngoods.ngoods_PL_Bas_MatPFloat b on a.CompanyID = b.CompanyID and a.MaterialProperty=b.MaterialProperty
                where A.CompanyID = '{UserInfo.UserCompanyId}' and isnull(a.MonthOrWeek,0)=0 and isnull(a.AreaOrShop,0)=0 and a.CompanyID='{companyId}' and a.Yearv='{year}' and a.Monthv='{month}' and a.ParentAreaID='{parentAreaId}' and a.KindID='{kindId}' and a.SeriesID='{seriesId}'
            )b 
            where a.CompanyID = '{UserInfo.UserCompanyId}' and  b.CompanyID=a.CompanyID and b.Yearv=a.Yearv and b.Monthv=a.Monthv and b.ParentAreaID=a.ParentAreaID and b.KindID=a.KindID and b.SeriesID=a.SeriesID  and b.MaterialProperty=a.MaterialProperty and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=0";

                var sqlAutoStandAmountRate = $@"update ngoods.ngoods_PL_MatP_Process a
            set AutoStandAmountRate=case when m.PtStandAmountRate=0 then 0 else a.PtStandAmountRate/m.PtStandAmountRate end, CkPtStandAmountRate=case when m.PtStandAmountRate=0 then 0 else a.PtStandAmountRate/m.PtStandAmountRate end
            from(
                select CompanyID,Yearv,Weekv,ParentAreaID,KindID,SeriesID,sum(PtStandAmountRate)as PtStandAmountRate
                from ngoods.ngoods_PL_MatP_Process 
                where CompanyID = '{UserInfo.UserCompanyId}' and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=0 and CompanyID='{companyId}' and Yearv='{year}' and Monthv='{month}' and ParentAreaID='{parentAreaId}' and KindID='{kindId}' and SeriesID='{seriesId}'
                group by CompanyID,Yearv,Weekv,ParentAreaID,KindID,SeriesID
            )m 
            where a.CompanyID = '{UserInfo.UserCompanyId}' and  m.CompanyID=a.CompanyID and m.Yearv=a.Yearv and m.Weekv=a.Weekv and m.KindID=a.KindID and m.ParentAreaID=a.ParentAreaID and m.SeriesID=a.SeriesID  and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=0";

                var sql = $@"update ngoods.ngoods_PL_MatP_Process a
            set AutoStandAmountRate=a.AutoStandAmountRate-b.DiffPt,CkPtStandAmountRate=a.AutoStandAmountRate-b.DiffPt
            from(
                select a.CompanyID,a.Yearv,a.Weekv,a.ParentAreaID,a.KindID,a.SeriesID,a.MaterialProperty,b.AutoStandAmountRate-1 as DiffPt, row_number()over(partition by a.Yearv,a.Weekv,a.ParentAreaID,a.KindID,a.SeriesID order by StandAmountRate desc) as RowNum
                from ngoods.ngoods_PL_MatP_Process a
                inner join (
                    select CompanyID,Yearv,Weekv,ParentAreaID,KindID,SeriesID,sum(AutoStandAmountRate) as AutoStandAmountRate
                    from ngoods.ngoods_PL_MatP_Process
                    where CompanyID = '{UserInfo.UserCompanyId}' and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=0
                    group by CompanyID,Yearv,Weekv,ParentAreaID,KindID,SeriesID
                    having sum(AutoStandAmountRate)<>1 and sum(AutoStandAmountRate)>0
                ) b on a.CompanyID=b.CompanyID and a.Yearv=b.Yearv and a.Weekv=b.Weekv and b.ParentAreaID=a.ParentAreaID and a.KindID=b.KindID and a.SeriesID=b.SeriesID 
                where a.CompanyID = '{UserInfo.UserCompanyId}' and a.CompanyID='{companyId}' and a.Yearv='{year}' and a.Monthv='{month}' and a.ParentAreaID='{parentAreaId}' and a.KindID='{kindId}' and a.SeriesID='{seriesId}' 
            ) b
            where a.CompanyID = '{UserInfo.UserCompanyId}' and b.RowNum=1 and a.CompanyID=b.CompanyID and a.ParentAreaID=b.ParentAreaID and a.Yearv=b.Yearv and a.Weekv=b.Weekv and a.KindID=b.KindID and a.SeriesID=b.SeriesID and a.MaterialProperty=b.MaterialProperty   and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=0";

                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo
                    {
                        CommandText = sqlPtStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlAutoStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 重算单店月的数据

        public void UpdateShopMonthData(string companyId, int year, int month, string parentAreaId, string kindId, string seriesId)
        {
            try
            {
                var sqlPtStandAmountRate = $@"update ngoods.ngoods_PL_MatP_Process a 
            set PtStandAmountRate = case when a.CalcStandAmountRate>PtStandAmountRate_p then PtStandAmountRate_p when a.CalcStandAmountRate<PtStandAmountRate_m then PtStandAmountRate_m  else a.CalcStandAmountRate  end
            from
            (
                select distinct a.CompanyID,a.Yearv,a.Monthv,a.ParentAreaID,a.KindID,a.SeriesID,a.MaterialProperty, CkPtStandAmountRate*(1+isnull(b.AreaShopFloat,0.2)) as PtStandAmountRate_p, CkPtStandAmountRate*(1-isnull(b.AreaShopFloat,0.2)) as PtStandAmountRate_m
                from ngoods.ngoods_PL_MatP_Process a
                left join ngoods.ngoods_PL_Bas_MatPFloat b on a.CompanyID = b.CompanyID and a.MaterialProperty=b.MaterialProperty 
                where a.CompanyID = '{UserInfo.UserCompanyId}' and isnull(a.MonthOrWeek,0)=0 and isnull(a.AreaOrShop,0)=0 and a.CompanyID='{companyId}' and a.Yearv='{year}' and a.Monthv='{month}' and a.ParentAreaID='{parentAreaId}' and a.KindID='{kindId}' and a.SeriesID='{seriesId}'
            )b 
            where a.CompanyID = '{UserInfo.UserCompanyId}' and b.CompanyID=a.CompanyID and b.Yearv=a.Yearv and b.Monthv=a.Monthv and b.ParentAreaID=a.ParentAreaID and b.KindID=a.KindID and b.SeriesID=a.SeriesID and b.MaterialProperty=a.MaterialProperty and isnull(MonthOrWeek,0)=0 and isnull(AreaOrShop,0)=1";

                var sqlAutoStandAmountRate = $@"update ngoods.ngoods_PL_MatP_Process a
            set AutoStandAmountRate=case when m.PtStandAmountRate=0 then 0 else a.PtStandAmountRate/m.PtStandAmountRate end, CkPtStandAmountRate=case when m.PtStandAmountRate=0 then 0 else a.PtStandAmountRate/m.PtStandAmountRate end
            from
            (
                select CompanyID,Yearv,Monthv,ParentAreaID,ShopID,KindID,SeriesID, sum(PtStandAmountRate)as PtStandAmountRate
                from ngoods.ngoods_PL_MatP_Process
                where CompanyID = '{UserInfo.UserCompanyId}' and isnull(MonthOrWeek,0)=0 and isnull(AreaOrShop,0)=1 and CompanyID='{companyId}' and Yearv='{year}' and Monthv='{month}' and ParentAreaID='{parentAreaId}' and KindID='{kindId}' and SeriesID='{seriesId}' 
                group by CompanyID,Yearv,Monthv,ParentAreaID,ShopID,KindID,SeriesID
            )m 
            where a.CompanyID = '{UserInfo.UserCompanyId}' and m.CompanyID=a.CompanyID and m.Yearv=a.Yearv and m.Monthv=a.Monthv and m.KindID=a.KindID and m.ParentAreaID=a.ParentAreaID and m.ShopID=a.ShopID and m.SeriesID=a.SeriesID  and isnull(MonthOrWeek,0)=0 and isnull(AreaOrShop,0)=1";

                var sql = $@"update ngoods.ngoods_PL_MatP_Process a
            set AutoStandAmountRate=a.AutoStandAmountRate-b.DiffPt,CkPtStandAmountRate=a.AutoStandAmountRate-b.DiffPt
            from
            (
                select a.CompanyID,a.Yearv,a.Monthv,a.ShopID,a.KindID,a.SeriesID,a.MaterialProperty,a.AutoStandAmountRate,b.AutoStandAmountRate-1 as DiffPt, row_number()over(partition by a.Yearv,a.Monthv,a.ShopID,a.KindID,a.SeriesID order by a.AutoStandAmountRate desc) as RowNum
                from ngoods.ngoods_PL_MatP_Process a
                inner join (
                    select CompanyID,Yearv,Monthv,ShopID,KindID,SeriesID,sum(AutoStandAmountRate) as AutoStandAmountRate
                    from ngoods.ngoods_PL_MatP_Process
                    where CompanyID = '{UserInfo.UserCompanyId}' and isnull(MonthOrWeek,0)=0 and isnull(AreaOrShop,0)=1
                    group by CompanyID,Yearv,Monthv,ShopID,KindID,SeriesID
                    having sum(AutoStandAmountRate)<>1 and sum(AutoStandAmountRate)>0
                ) b 
                on a.CompanyID=b.CompanyID and a.Yearv=b.Yearv and b.Monthv=a.Monthv and a.ShopID=b.ShopID  and a.KindID=b.KindID and a.SeriesID=b.SeriesID
                where a.CompanyID = '{UserInfo.UserCompanyId}' and isnull(a.MonthOrWeek,0)=0 and isnull(a.AreaOrShop,0)=1  and a.CompanyID='{companyId}' and a.Yearv='{year}' and a.Monthv='{month}' and a.ParentAreaID='{parentAreaId}' and a.KindID='{kindId}' and a.SeriesID='{seriesId}' 
            ) b
            where a.CompanyID = '{UserInfo.UserCompanyId}' and b.RowNum=1 and a.CompanyID=b.CompanyID and a.Yearv=b.Yearv and a.Monthv=b.Monthv and a.ShopID=b.ShopID and a.KindID=b.KindID and a.SeriesID=b.SeriesID and a.MaterialProperty=b.MaterialProperty and isnull(MonthOrWeek,0)=0 and isnull(AreaOrShop,0)=1";

                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo
                    {
                        CommandText = sqlPtStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlAutoStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 重算单店周的数据

        public void UpdateShopWeekData(string companyId, int year, int month, string parentAreaId, string kindId, string seriesId)
        {
            try
            {
                var sqlPtStandAmountRate = $@"update ngoods.ngoods_PL_MatP_Process a 
        set PtStandAmountRate = case when a.CalcStandAmountRate>b.PtStandAmountRate_p then b.PtStandAmountRate_p  when a.CalcStandAmountRate<b.PtStandAmountRate_m then b.PtStandAmountRate_m  else a.CalcStandAmountRate  end
		from (          
			select distinct a.CompanyID,a.ParentAreaID,b.ShopID,a.Yearv,a.Monthv,a.Weekv,a.KindID,a.SeriesID,a.MaterialProperty,	case when a.PtStandAmountRate_p>=b.PtStandAmountRate_p then a.PtStandAmountRate_p else b.PtStandAmountRate_p end as PtStandAmountRate_p,	case when a.PtStandAmountRate_m>=b.PtStandAmountRate_m then b.PtStandAmountRate_m else a.PtStandAmountRate_m end as PtStandAmountRate_m
			from 
			(
                --区域 周 
                select distinct a.CompanyID,a.ParentAreaID,a.Yearv,a.Monthv,a.Weekv,a.KindID,a.SeriesID,a.MaterialProperty,CkPtStandAmountRate*(1+isnull(b.AreaShopFloat,0.2)) as PtStandAmountRate_p,CkPtStandAmountRate*(1-isnull(b.AreaShopFloat,0.2)) as PtStandAmountRate_m
                from ngoods.ngoods_PL_MatP_Process A
                left join ngoods.ngoods_PL_Bas_MatPFloat b on a.CompanyID = b.CompanyID and a.MaterialProperty=b.MaterialProperty 
                where A.CompanyID = '{UserInfo.UserCompanyId}' and isnull(a.MonthOrWeek,0)=1 and isnull(a.AreaOrShop,0)=0
			) a 
			inner join 
			(
                --单店 月
                select distinct a.CompanyID,a.ParentAreaID,a.ShopID,a.Yearv,a.Monthv,a.KindID,a.SeriesID,a.MaterialProperty,CkPtStandAmountRate*(1+isnull(b.MonthWeekFloat,0.1)) as PtStandAmountRate_p,CkPtStandAmountRate*(1-isnull(b.MonthWeekFloat,0.1)) as PtStandAmountRate_m
                from ngoods.ngoods_PL_MatP_Process A
                left join ngoods.ngoods_PL_Bas_MatPFloat b on a.CompanyID = b.CompanyID and a.MaterialProperty=b.MaterialProperty
                where A.CompanyID = '{UserInfo.UserCompanyId}' and isnull(a.MonthOrWeek,0)=0 and isnull(a.AreaOrShop,0)=1
			) b 
			on b.CompanyID=a.CompanyID and b.Yearv=a.Yearv and b.Monthv=a.Monthv and b.ParentAreaID=a.ParentAreaID and b.KindID=a.KindID and b.SeriesID=a.SeriesID and b.MaterialProperty=a.MaterialProperty 
            where A.CompanyID = '{UserInfo.UserCompanyId}' and a.CompanyID='{companyId}' and a.Yearv='{year}' and a.Monthv='{month}' and a.ParentAreaID='{parentAreaId}' and a.KindID='{kindId}' and a.SeriesID='{seriesId}' 
		)b 
		where a.CompanyID = '{UserInfo.UserCompanyId}' and b.CompanyID=a.CompanyID and b.Yearv=a.Yearv and b.Monthv=a.Monthv and b.Weekv=a.Weekv and b.ParentAreaID=a.ParentAreaID and b.ShopID=a.ShopID and b.KindID=a.KindID and b.SeriesID=a.SeriesID and b.MaterialProperty=a.MaterialProperty and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=1";

                var sqlAutoStandAmountRate = $@"update ngoods.ngoods_PL_MatP_Process a
            set AutoStandAmountRate=case when m.PtStandAmountRate=0 then 0 else a.PtStandAmountRate/m.PtStandAmountRate end,
                CkPtStandAmountRate=case when m.PtStandAmountRate=0 then 0 else a.PtStandAmountRate/m.PtStandAmountRate end
            from(
                select CompanyID,Yearv,Monthv,Weekv,ParentAreaID,ShopID,KindID,SeriesID,sum(PtStandAmountRate)as PtStandAmountRate
                from ngoods.ngoods_PL_MatP_Process
                where CompanyID = '{UserInfo.UserCompanyId}' and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=1 and CompanyID='{companyId}' and Yearv='{year}' and Monthv='{month}' and ParentAreaID='{parentAreaId}' and KindID='{kindId}' and SeriesID='{seriesId}'
                group by CompanyID,Yearv,Monthv,Weekv,ParentAreaID,ShopID,KindID,SeriesID
            )m 
            where m.CompanyID=a.CompanyID and m.Yearv=a.Yearv and m.Monthv=a.Monthv and m.Weekv=a.Weekv and m.ParentAreaID=a.ParentAreaID and m.ShopID=a.ShopID and m.SeriesID=a.SeriesID and m.KindID=a.KindID and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=1";

                var sql = $@"update ngoods.ngoods_PL_MatP_Process a
            set AutoStandAmountRate=a.AutoStandAmountRate-b.DiffPt, CkPtStandAmountRate=a.AutoStandAmountRate-b.DiffPt
            from
            (
                select a.CompanyID,a.Yearv,a.Weekv,a.ParentAreaID,a.ShopID,a.KindID,a.SeriesID,a.MaterialProperty, a.AutoStandAmountRate,b.AutoStandAmountRate-1 as DiffPt,  row_number()over(partition by a.Yearv,a.Weekv,a.ParentAreaID,a.ShopID,a.KindID,a.SeriesID order by a.AutoStandAmountRate desc) as RowNum
                from ngoods.ngoods_PL_MatP_Process a
                inner join (
                    select CompanyID,Yearv,Weekv,ParentAreaID,ShopID,KindID,SeriesID,sum(AutoStandAmountRate) as AutoStandAmountRate
                    from ngoods.ngoods_PL_MatP_Process
                    where CompanyID = '{UserInfo.UserCompanyId}' and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=1
                    group by CompanyID,Yearv,Weekv,ParentAreaID,ShopID,KindID,SeriesID
                    having sum(AutoStandAmountRate)<>1 and sum(AutoStandAmountRate)>0
                ) b 
                on a.CompanyID=b.CompanyID and a.Yearv=b.Yearv and a.Weekv=b.Weekv and b.ParentAreaID=a.ParentAreaID and b.ShopID=a.ShopID and a.KindID=b.KindID and a.SeriesID=b.SeriesID
                where a.CompanyID = '{UserInfo.UserCompanyId}' and isnull(a.MonthOrWeek,0)=1 and isnull(a.AreaOrShop,0)=1 and a.CompanyID='{companyId}' and a.Yearv='{year}' and a.Monthv='{month}' and a.ParentAreaID='{parentAreaId}' and a.KindID='{kindId}' and a.SeriesID='{seriesId}'
            ) b
            where a.CompanyID = '{UserInfo.UserCompanyId}' and  b.RowNum=1 and a.CompanyID=b.CompanyID and a.ParentAreaID=b.ParentAreaID and a.ShopID=b.ShopID and a.Yearv=b.Yearv and a.Weekv=b.Weekv and a.KindID=b.KindID and a.SeriesID=b.SeriesID and a.MaterialProperty=b.MaterialProperty  and isnull(MonthOrWeek,0)=1 and isnull(AreaOrShop,0)=1";

                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo
                    {
                        CommandText = sqlPtStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlAutoStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion


        enum MonthOrWeekEnum
        {
            月 = 0,
            周 = 1
        }

        enum AreaOrShopEnum
        {
            区域 = 0,
            单店 = 1
        }



    }
}
